using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web.Http;
using WebAPI.Models;

namespace WebAPI.Controllers
{
    public class Gy_SteppedPriceCoefficientController : ApiController
    {
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;
        private json objJsonResult = new json();
        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        DataSet ds;

        #region 新增：工序唯一性检查
        [Route("Gy_SteppedPriceCoefficientBill/CheckProcessUnique")]
        [HttpPost]
        public object CheckProcessUnique([FromBody] JObject data)
        {
            try
            {
                Console.WriteLine("=== 工序唯一性检查开始 ===");

                string HProcID = data["HProcID"]?.ToString() ?? "";
                string HStockOrgID = data["HStockOrgID"]?.ToString() ?? "";
                string ExcludeID = data["ExcludeID"]?.ToString() ?? "0";
                string user = data["user"]?.ToString() ?? "";

                Console.WriteLine($"参数: HProcID={HProcID}, HStockOrgID={HStockOrgID}, ExcludeID={ExcludeID}, user={user}");

                if (string.IsNullOrEmpty(HProcID) || HProcID == "0")
                {
                    return new
                    {
                        code = "1",
                        data = new { exists = false, message = "" }
                    };
                }

                if (string.IsNullOrEmpty(HStockOrgID) || HStockOrgID == "0")
                {
                    return new
                    {
                        code = "0",
                        Message = "组织不能为空"
                    };
                }

                // 查询该工序是否已存在任何单据中（无论状态如何）
                // 特别注意：这里检查的是同一个工序ID在同一个组织下是否已存在
                string sql = $@"
                    SELECT TOP 1 HBillNo, HBillStatus, HDeleteMan 
                    FROM Gy_SteppedPriceCoefficientBillMain 
                    WHERE HProcID = {HProcID} 
                      AND HStockOrgID = {HStockOrgID} 
                      AND HInterID != {ExcludeID}";

                Console.WriteLine($"查询SQL: {sql}");

                ds = oCN.RunProcReturn(sql, "CheckProcess");

                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    string billNo = ds.Tables[0].Rows[0]["HBillNo"]?.ToString() ?? "";
                    int billStatus = ds.Tables[0].Rows[0]["HBillStatus"] == DBNull.Value ? 1 : Convert.ToInt32(ds.Tables[0].Rows[0]["HBillStatus"]);
                    string deleteMan = ds.Tables[0].Rows[0]["HDeleteMan"]?.ToString() ?? "";

                    Console.WriteLine($"找到重复记录: BillNo={billNo}, BillStatus={billStatus}, DeleteMan={deleteMan}");

                    // 只要有记录就返回存在（无论什么状态）
                    return new
                    {
                        code = "1",
                        data = new
                        {
                            exists = true,
                            billNo = billNo,
                            message = $"该工序已在单据[{billNo}]中使用，一个工序只能对应一条阶梯工价系数数据！"
                        }
                    };
                }

                Console.WriteLine("未找到重复记录，工序可用");

                return new
                {
                    code = "1",
                    data = new { exists = false }
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine($"工序唯一性检查异常: {ex.Message}");
                return new
                {
                    code = "0",
                    Message = "检查工序唯一性时出错：" + ex.Message
                };
            }
        }
        #endregion

        #region 修改：保存方法，添加工序唯一性验证
        [Route("Gy_SteppedPriceCoefficientBill/ModifyByID")]
        [HttpPost]
        public object ModifyByID([FromBody] JObject oMain)
        {
            bool transactionStarted = false;
            string HBillNo = "";
            long newHInterID = 0;
            List<string> errorLogs = new List<string>();

            try
            {
                errorLogs.Add($"=== {DateTime.Now:yyyy-MM-dd HH:mm:ss} 保存开始 ===");

                // 1. 解析数据
                var _value = oMain["oMain"].ToString();
                string[] sArray = _value.Split(';');
                string jsonData = sArray[0];
                string user = sArray.Length > 1 ? sArray[1] : "unknown";

                errorLogs.Add($"解析数据: user={user}");

                // 2. 解析JSON
                jsonData = "[" + jsonData + "]";
                JArray jArray = JArray.Parse(jsonData);

                if (jArray.Count == 0)
                {
                    return new { code = "0", Message = "数据不能为空！" };
                }

                JToken jToken = jArray[0];

                // 3. 提取字段
                long HInterID = 0;
                if (jToken["HInterID"] != null && long.TryParse(jToken["HInterID"].ToString(), out long tempId))
                {
                    HInterID = tempId;
                }

                string HBillNoFromJson = jToken["HBillNo"]?.ToString() ?? "";
                string HRemark = jToken["HRemark"]?.ToString() ?? "";

                // 数值字段
                int HProcID = 0, HEmpID = 0, HDeptID = 0, HStockOrgID = 0;
                if (jToken["HProcID"] != null) int.TryParse(jToken["HProcID"].ToString(), out HProcID);
                if (jToken["HEmpID"] != null) int.TryParse(jToken["HEmpID"].ToString(), out HEmpID);
                if (jToken["HDeptID"] != null) int.TryParse(jToken["HDeptID"].ToString(), out HDeptID);
                if (jToken["HStockOrgID"] != null) int.TryParse(jToken["HStockOrgID"].ToString(), out HStockOrgID);

                errorLogs.Add($"提取字段: HInterID={HInterID}, HProcID={HProcID}, HStockOrgID={HStockOrgID}");

                // 日期字段
                DateTime HDate = DateTime.Now;
                if (jToken["HDate"] != null && DateTime.TryParse(jToken["HDate"].ToString(), out DateTime tempDate))
                {
                    HDate = tempDate;
                }

                // 4. 验证基础数据
                if (string.IsNullOrEmpty(HBillNoFromJson))
                {
                    return new { code = "0", Message = "单据号不能为空！" };
                }

                if (HInterID <= 0)
                {
                    return new { code = "0", Message = "单据ID无效！" };
                }

                if (HProcID == 0)
                {
                    return new { code = "0", Message = "工序不能为空！" };
                }

                if (HStockOrgID == 0)
                {
                    return new { code = "0", Message = "组织不能为空！" };
                }

                // 5. 检查工序唯一性
                errorLogs.Add($"开始检查工序唯一性");

                bool isEditMode = false;
                string originalProcID = "";

                // 检查是否已存在
                string checkExistSql = $"SELECT COUNT(*), HProcID FROM Gy_SteppedPriceCoefficientBillMain WHERE HInterID = {HInterID} GROUP BY HProcID";
                DataSet existDs = oCN.RunProcReturn(checkExistSql, "CheckExist");

                if (existDs != null && existDs.Tables[0].Rows.Count > 0)
                {
                    // 编辑模式
                    isEditMode = true;
                    originalProcID = existDs.Tables[0].Rows[0]["HProcID"]?.ToString() ?? "";
                    errorLogs.Add($"编辑模式: 原始工序ID={originalProcID}");
                }
                else
                {
                    // 新增模式
                    errorLogs.Add($"新增模式");
                }

                // 判断是否需要检查工序唯一性
                bool needCheckProcess = true;

                if (isEditMode && !string.IsNullOrEmpty(originalProcID))
                {
                    // 编辑模式：如果工序ID没有变化，则不需要检查
                    if (originalProcID == HProcID.ToString())
                    {
                        needCheckProcess = false;
                        errorLogs.Add($"工序未修改，跳过唯一性检查");
                    }
                    else
                    {
                        errorLogs.Add($"工序从{originalProcID}修改为{HProcID}，需要检查唯一性");
                    }
                }

                // 执行工序唯一性检查
                if (needCheckProcess)
                {
                    string checkProcessSql = $@"
                        SELECT TOP 1 HBillNo 
                        FROM Gy_SteppedPriceCoefficientBillMain 
                        WHERE HProcID = {HProcID} 
                          AND HStockOrgID = {HStockOrgID} 
                          AND HInterID != {HInterID}";

                    DataSet processDs = oCN.RunProcReturn(checkProcessSql, "CheckProcess");

                    if (processDs != null && processDs.Tables[0].Rows.Count > 0)
                    {
                        string existingBillNo = processDs.Tables[0].Rows[0]["HBillNo"]?.ToString() ?? "";
                        errorLogs.Add($"发现重复工序: 单据号={existingBillNo}");

                        return new
                        {
                            code = "0",
                            Message = $"该工序已在单据[{existingBillNo}]中使用，一个工序只能对应一条阶梯工价系数数据！",
                            logs = errorLogs
                        };
                    }
                    errorLogs.Add($"工序唯一性检查通过");
                }

                // 6. 开始事务
                oCN.BeginTran();
                transactionStarted = true;

                newHInterID = HInterID;
                HBillNo = HBillNoFromJson;

                // 安全处理字符串
                string safeHBillNo = HBillNo.Replace("'", "''");
                string safeHRemark = HRemark.Replace("'", "''");
                string safeUser = user.Replace("'", "''");

                // 7. 保存主表数据
                if (isEditMode)
                {
                    // 编辑模式
                    errorLogs.Add($"HInterID={HInterID} 已存在，进入编辑模式");

                    // 更新主表
                    string updateSql = $@"
                        UPDATE Gy_SteppedPriceCoefficientBillMain SET 
                            HDate = '{HDate.ToString("yyyy-MM-dd HH:mm:ss")}', 
                            HRemark = '{safeHRemark}', 
                            HProcID = {HProcID}, 
                            HEmpID = {HEmpID}, 
                            HDeptID = {HDeptID}, 
                            HStockOrgID = {HStockOrgID}, 
                            HUpDater = '{safeUser}', 
                            HUpDateDate = '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' 
                        WHERE HInterID = {HInterID}";

                    errorLogs.Add($"执行更新SQL: {updateSql}");
                    oCN.RunProc(updateSql, ref DBUtility.ClsPub.sExeReturnInfo);

                    // 删除原有子表
                    string deleteSubSql = $"DELETE FROM Gy_SteppedPriceCoefficientBillSub WHERE HInterID = {HInterID}";
                    oCN.RunProc(deleteSubSql, ref DBUtility.ClsPub.sExeReturnInfo);
                }
                else
                {
                    // 新增模式
                    errorLogs.Add($"HInterID={HInterID} 不存在，进入新增模式");

                    // 获取年份和期间
                    int HYear = HDate.Year;
                    int HPeriod = HDate.Month;

                    // 构建主表插入SQL
                    string mainSql = $@"
                        INSERT INTO Gy_SteppedPriceCoefficientBillMain (
                            HYear, HPeriod, HBillType, HBillSubType,
                            HInterID, HDate, HBillNo, HBillStatus, HRemark,
                            HBackRemark, HMaker, HMakeDate,
                            HMainSourceBillType, HMainSourceInterID, HMainSourceEntryID, HMainSourceBillNo,
                            HPrintQty, HProcID, HEmpID, HDeptID, HStockOrgID
                        ) VALUES (
                            {HYear}, {HPeriod}, 'GYST', '',
                            {HInterID}, '{HDate.ToString("yyyy-MM-dd HH:mm:ss")}', '{safeHBillNo}', 1, '{safeHRemark}',
                            '', '{safeUser}', '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',
                            '', 0, 0, '', 0, 
                            {HProcID}, {HEmpID}, {HDeptID}, {HStockOrgID}
                        )";

                    errorLogs.Add($"执行主表插入SQL");
                    oCN.RunProc(mainSql, ref DBUtility.ClsPub.sExeReturnInfo);
                }

                // 8. 插入子表
                if (jToken["SubItems"] != null)
                {
                    JArray subItems = jToken["SubItems"] as JArray;

                    if (subItems != null && subItems.Count > 0)
                    {
                        errorLogs.Add($"开始插入{subItems.Count}条子表数据");

                        for (int i = 0; i < subItems.Count; i++)
                        {
                            JToken subToken = subItems[i];

                            int hSeq = subToken["HSeq"]?.ToObject<int>() ?? (i + 1);
                            int hMinQty = subToken["HMinQty"]?.ToObject<int>() ?? 0;
                            int hMaxQty = subToken["HMaxQty"]?.ToObject<int>() ?? 0;
                            decimal hPriceCoefficient = subToken["HPriceCoefficient"]?.ToObject<decimal>() ?? 1.0m;
                            decimal hMaxPrice = subToken["HMaxPrice"]?.ToObject<decimal>() ?? 0m;
                            string hRemark = subToken["HRemark"]?.ToString() ?? "";

                            string safeSubRemark = hRemark.Replace("'", "''");

                            string insertSubSql = $@"
                                INSERT INTO Gy_SteppedPriceCoefficientBillSub (
                                    HInterID, HBillNo_bak, HEntryID, HRemark,
                                    HSourceInterID, HSourceEntryID, HSourceBillNo, HSourceBillType,
                                    HRelationQty, HRelationMoney, HSeq,
                                    HMinQty, HMaxQty, HPriceCoefficient,
                                    HMaxPrice, HStockOrgID
                                ) VALUES (
                                    {HInterID}, '{safeHBillNo}', {i + 1}, '{safeSubRemark}',
                                    0, 0, '', '', 0, 0,
                                    {hSeq}, {hMinQty}, {hMaxQty},
                                    {hPriceCoefficient.ToString().Replace(",", ".")},
                                    {(hMaxPrice == 0 ? "NULL" : hMaxPrice.ToString().Replace(",", "."))},
                                    {HStockOrgID}
                                )";

                            oCN.RunProc(insertSubSql, ref DBUtility.ClsPub.sExeReturnInfo);
                        }
                        errorLogs.Add($"子表数据插入完成");
                    }
                }

                // 9. 提交事务
                oCN.Commit();
                transactionStarted = false;

                errorLogs.Add($"=== 保存成功完成 ===");

                return new
                {
                    code = "1",
                    Message = "保存成功！",
                    data = new { HInterID = newHInterID, HBillNo = HBillNo },
                    logs = errorLogs
                };
            }
            catch (Exception ex)
            {
                errorLogs.Add($"=== 发生异常 ===");
                errorLogs.Add($"异常信息: {ex.Message}");
                errorLogs.Add($"堆栈跟踪: {ex.StackTrace}");

                // 回滚事务
                if (transactionStarted)
                {
                    try { oCN.RollBack(); errorLogs.Add("事务已回滚"); } catch { errorLogs.Add("事务回滚失败"); }
                }

                return new
                {
                    code = "0",
                    Message = $"保存失败：{ex.Message}",
                    logs = errorLogs
                };
            }
        }
        #endregion

        #region 新增：获取工序对应的名称
        [Route("Gy_SteppedPriceCoefficientBill/GetProcessName")]
        [HttpGet]
        public object GetProcessName(int HProcID, int HStockOrgID)
        {
            try
            {
                if (HProcID == 0)
                {
                    return new { code = "0", Message = "工序ID不能为空" };
                }

                // 查询工序名称，这里需要根据你的数据库结构调整
                string sql = $@"
                    SELECT TOP 1 
                        FItemID as HProcID,
                        FName as HProcName
                    FROM t_ICItem  -- 这里需要替换成你的工序表名
                    WHERE FItemID = {HProcID} 
                      AND FStockOrgID = {HStockOrgID}";

                // 如果工序信息不在t_ICItem表中，需要调整查询
                // 先尝试用通用查询
                DataSet nameDs = oCN.RunProcReturn(sql, "ProcessName");

                if (nameDs != null && nameDs.Tables[0].Rows.Count > 0)
                {
                    return new
                    {
                        code = "1",
                        data = new
                        {
                            HProcID = HProcID,
                            HProcName = nameDs.Tables[0].Rows[0]["HProcName"]?.ToString() ?? $"工序{HProcID}"
                        }
                    };
                }
                else
                {
                    // 如果查不到，返回默认值
                    return new
                    {
                        code = "1",
                        data = new
                        {
                            HProcID = HProcID,
                            HProcName = $"工序{HProcID}"
                        }
                    };
                }
            }
            catch (Exception ex)
            {
                return new { code = "0", Message = "获取工序名称失败：" + ex.Message };
            }
        }
        #endregion

        #region 查询
        [Route("Gy_SteppedPriceCoefficientBill/list")]
        [HttpGet]
        public object getSteppedPriceCoefficient(string sWhere = "", string user = "", int page = 1, int size = 50)
        {
            try
            {
                // 添加调试信息
                Console.WriteLine("=== 查询开始 ===");
                Console.WriteLine($"参数: sWhere={sWhere}, user={user}, page={page}, size={size}");

                // 确保user不为空
                if (string.IsNullOrEmpty(user))
                {
                    user = "admin";
                    Console.WriteLine("警告: user参数为空，使用默认值: admin");
                }

                // 构建查询SQL
                string baseSql = @"
                    SELECT 
                        m.HInterID,
                        m.HItemMainID,
                        m.HBillNo,
                        m.HDate,
                        m.HYear,
                        m.HPeriod,
                        m.HBillStatus,
                        m.HRemark,
                        m.HChecker,
                        m.HCheckDate,
                        m.HMaker,
                        m.HMakeDate,
                        m.HUpDater,
                        m.HUpDateDate,
                        m.HCloseMan,
                        m.HCloseDate,
                        m.HProcID,
                        m.HEmpID,
                        m.HDeptID,
                        m.HStockOrgID,
                        -- 子表统计信息
                        ISNULL((SELECT COUNT(*) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as SubCount,
                        ISNULL((SELECT SUM(HMinQty) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalMinQty,
                        ISNULL((SELECT SUM(HMaxQty) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalMaxQty,
                        ISNULL((SELECT SUM(HPriceCoefficient) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalCoefficient,
                        ISNULL((SELECT SUM(HMaxPrice) FROM Gy_SteppedPriceCoefficientBillSub s WHERE s.HInterID = m.HInterID), 0) as TotalMaxPrice
                    FROM Gy_SteppedPriceCoefficientBillMain m 
                    WHERE m.HDeleteMan = '' ";

                string whereClause = "";

                if (!string.IsNullOrEmpty(sWhere))
                {
                    // 处理HMakeDate查询条件 - 确保正确转换
                    if (sWhere.Contains("HMakeDate"))
                    {
                        sWhere = System.Text.RegularExpressions.Regex.Replace(
                            sWhere,
                            @"HMakeDate\s*(>=|<=|>|<|=)\s*'([^']*)'",
                            match => $"CONVERT(date, HMakeDate) {match.Groups[1].Value} '{match.Groups[2].Value}'"
                        );
                    }

                    whereClause = " AND " + sWhere;
                }

                Console.WriteLine($"完整查询条件: {whereClause}");

                // 1. 先获取总记录数
                string countSql = "SELECT COUNT(*) as TotalCount FROM Gy_SteppedPriceCoefficientBillMain WHERE HDeleteMan = '' " + whereClause;
                Console.WriteLine($"统计SQL: {countSql}");

                DataSet dsCount = oCN.RunProcReturn(countSql, "TotalCount");
                int totalCount = 0;

                if (dsCount != null && dsCount.Tables.Count > 0 && dsCount.Tables[0].Rows.Count > 0)
                {
                    object countValue = dsCount.Tables[0].Rows[0]["TotalCount"];
                    if (countValue != null && countValue != DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(countValue);
                    }
                }

                Console.WriteLine($"总记录数: {totalCount}");

                // 2. 执行分页查询
                int startRow = (page - 1) * size;
                string pageSql = $@"
                    SELECT * FROM (
                        SELECT ROW_NUMBER() OVER (ORDER BY HInterID DESC) AS RowNum, *
                        FROM ({baseSql + whereClause}) AS T
                    ) AS T2
                    WHERE RowNum > {startRow} AND RowNum <= {startRow + size}";

                Console.WriteLine($"分页查询SQL: {pageSql}");

                ds = oCN.RunProcReturn(pageSql, "Gy_SteppedPriceCoefficientList");

                // 调试信息
                if (ds == null)
                {
                    Console.WriteLine("查询返回的DataSet为null");
                }
                else
                {
                    Console.WriteLine($"查询返回的DataSet表数量: {ds.Tables.Count}");
                    if (ds.Tables.Count > 0)
                    {
                        Console.WriteLine($"第一张表行数: {ds.Tables[0].Rows.Count}");
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            Console.WriteLine("第一行数据示例:");
                            foreach (DataColumn col in ds.Tables[0].Columns)
                            {
                                Console.WriteLine($"  {col.ColumnName}: {ds.Tables[0].Rows[0][col.ColumnName]}");
                            }
                        }
                    }
                }

                List<object> columnNameList = new List<object>();

                // 3. 获取列信息
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataColumn col in ds.Tables[0].Columns)
                    {
                        Type dataType = col.DataType;
                        string colName = col.ColumnName;
                        string typeName = dataType.Name;

                        columnNameList.Add(new
                        {
                            ColmCols = colName,
                            ColmType = typeName
                        });
                    }

                    // 添加子表相关的列
                    columnNameList.Add(new { ColmCols = "SubCount", ColmType = "Int32" });
                    columnNameList.Add(new { ColmCols = "TotalMinQty", ColmType = "Decimal" });
                    columnNameList.Add(new { ColmCols = "TotalMaxQty", ColmType = "Decimal" });
                    columnNameList.Add(new { ColmCols = "TotalCoefficient", ColmType = "Decimal" });
                    columnNameList.Add(new { ColmCols = "TotalMaxPrice", ColmType = "Decimal" });
                }

                // 4. 将DataTable转换为对象列表（前端layui table需要数组格式）
                List<Dictionary<string, object>> dataList = new List<Dictionary<string, object>>();
                if (ds != null && ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        Dictionary<string, object> rowDict = new Dictionary<string, object>();
                        foreach (DataColumn col in ds.Tables[0].Columns)
                        {
                            rowDict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
                        }
                        dataList.Add(rowDict);
                    }
                }

                // 5. 构建返回结果
                var result = new
                {
                    code = "1",
                    count = totalCount,
                    Message = totalCount > 0 ? $"查询成功，共{totalCount}条记录" : "查询成功，无数据",
                    data = dataList,  // 使用转换后的列表
                    list = columnNameList,
                    debug = new
                    {
                        totalCount = totalCount,
                        returnedCount = dataList.Count,
                        queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }
                };

                Console.WriteLine($"查询完成，返回{dataList.Count}条数据");

                return result;
            }
            catch (Exception e)
            {
                Console.WriteLine($"=== 查询异常 ===");
                Console.WriteLine($"错误信息: {e.Message}");
                Console.WriteLine($"堆栈跟踪: {e.StackTrace}");

                var errorResult = new
                {
                    code = "0",
                    count = 0,
                    Message = $"查询失败！错误：{e.Message}",
                    data = new List<object>(),
                    list = new List<object>(),
                    debug = new
                    {
                        error = e.Message,
                        stackTrace = e.StackTrace,
                        queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }
                };

                return errorResult;
            }
        }
        #endregion

        #region 阶梯工价系数 编辑-页面赋值
        [Route("Gy_SteppedPriceCoefficientBill/editInit")]
        [HttpGet]
        public object getSteppedPriceCoefficientEditInit(string HInterID, string user)
        {
            try
            {
                Console.WriteLine($"=== 编辑页面初始化开始 ===");
                Console.WriteLine($"参数 - HInterID: {HInterID}, User: {user}");

                if (string.IsNullOrEmpty(HInterID) || HInterID == "0")
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                // 查询主表数据
                string mainSql = $@"
                    SELECT 
                        m.HInterID,
                        m.HItemMainID,
                        m.HBillNo,
                        m.HDate,
                        m.HRemark,
                        m.HProcID,
                        m.HEmpID,
                        m.HDeptID,
                        m.HStockOrgID,
                        m.HMaker,
                        m.HUpDater,
                        m.HChecker,
                        m.HCloseMan,
                        m.HDeleteMan,
                        m.HBacker,
                        m.HMakeDate,
                        m.HUpDateDate,
                        m.HCheckDate,
                        m.HCloseDate,
                        m.HDeleteDate,
                        m.HBackDate,
                        m.HBackRemark
                    FROM Gy_SteppedPriceCoefficientBillMain m
                    WHERE m.HInterID = {HInterID}";

                ds = oCN.RunProcReturn(mainSql, "MainData");

                Console.WriteLine($"主表查询结果行数: {ds?.Tables[0]?.Rows?.Count ?? 0}");

                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "未查询到记录！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                // 检查单据状态
                string checker = ds.Tables[0].Rows[0]["HChecker"]?.ToString() ?? "";
                if (!string.IsNullOrEmpty(checker))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "该单据已审核，不可编辑！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                // 获取子表数据
                string subSql = $@"
                    SELECT 
                        HEntryID,
                        HSeq,
                        HMinQty,
                        HMaxQty,
                        HPriceCoefficient,
                        HMaxPrice,
                        HRemark,
                        HStockOrgID
                    FROM Gy_SteppedPriceCoefficientBillSub 
                    WHERE HInterID = {HInterID} 
                    ORDER BY HSeq";

                DataSet subDs = oCN.RunProcReturn(subSql, "SubData");

                Console.WriteLine($"子表查询结果行数: {subDs?.Tables[0]?.Rows?.Count ?? 0}");

                // 转换数据为对象列表
                List<Dictionary<string, object>> mainList = new List<Dictionary<string, object>>();
                List<Dictionary<string, object>> subList = new List<Dictionary<string, object>>();

                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        Dictionary<string, object> rowDict = new Dictionary<string, object>();
                        foreach (DataColumn col in ds.Tables[0].Columns)
                        {
                            rowDict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
                        }
                        mainList.Add(rowDict);
                    }
                }

                if (subDs != null && subDs.Tables.Count > 0 && subDs.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow row in subDs.Tables[0].Rows)
                    {
                        Dictionary<string, object> rowDict = new Dictionary<string, object>();
                        foreach (DataColumn col in subDs.Tables[0].Columns)
                        {
                            rowDict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
                        }
                        subList.Add(rowDict);
                    }
                }

                // 构建返回结果
                var result = new
                {
                    Main = mainList,
                    Sub = subList
                };

                Console.WriteLine("编辑页面初始化成功完成");

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Success！";
                objJsonResult.data = result;
                return objJsonResult;
            }
            catch (Exception e)
            {
                Console.WriteLine($"=== 编辑页面初始化异常 ===");
                Console.WriteLine($"错误信息: {e.Message}");
                Console.WriteLine($"堆栈跟踪: {e.StackTrace}");

                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "查询失败！" + e.Message;
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 阶梯工价系数 删除
        [Route("Gy_SteppedPriceCoefficientBill/delete")]
        [HttpGet]
        public object deleteSteppedPriceCoefficient(string HInterID, string user)
        {
            try
            {
                if (string.IsNullOrEmpty(HInterID))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //检查单据状态
                string sql = "SELECT HBillStatus, HChecker FROM Gy_SteppedPriceCoefficientBillMain WHERE HInterID = " + HInterID;
                ds = oCN.RunProcReturn(sql, "CheckStatus");

                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "单据不存在！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string checker = ds.Tables[0].Rows[0]["HChecker"].ToString();
                if (!string.IsNullOrEmpty(checker))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "该单据已审核，不可删除！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                oCN.BeginTran();

                //删除子表数据
                string deleteSubSql = "DELETE FROM Gy_SteppedPriceCoefficientBillSub WHERE HInterID = " + HInterID;
                oCN.RunProc(deleteSubSql, ref DBUtility.ClsPub.sExeReturnInfo);

                //删除主表数据
                string deleteMainSql = "DELETE FROM Gy_SteppedPriceCoefficientBillMain WHERE HInterID = " + HInterID;
                oCN.RunProc(deleteMainSql, ref DBUtility.ClsPub.sExeReturnInfo);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "删除成功！";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "删除失败！" + e.Message;
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 阶梯工价系数 审核/反审核
        [Route("Gy_SteppedPriceCoefficientBill/Audit")]
        [HttpGet]
        public object AuditSteppedPriceCoefficient(int HInterID, int IsAudit, string CurUserName)
        {
            try
            {
                //检查单据状态
                string checkSql = "SELECT HBillStatus, HChecker FROM Gy_SteppedPriceCoefficientBillMain WHERE HInterID = " + HInterID;
                ds = oCN.RunProcReturn(checkSql, "CheckStatus");

                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "单据不存在！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string checker = ds.Tables[0].Rows[0]["HChecker"].ToString();
                int billStatus = Convert.ToInt32(ds.Tables[0].Rows[0]["HBillStatus"]);

                oCN.BeginTran();

                if (IsAudit == 0)  //审核
                {
                    if (!string.IsNullOrEmpty(checker))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已审核，不能重复审核！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    string auditSql = "UPDATE Gy_SteppedPriceCoefficientBillMain SET " +
                        "HBillStatus = 2, " +
                        "HChecker = '" + CurUserName + "', " +
                        "HCheckDate = GETDATE() " +
                        "WHERE HInterID = " + HInterID;

                    oCN.RunProc(auditSql, ref DBUtility.ClsPub.sExeReturnInfo);

                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "审核成功！";
                }
                else if (IsAudit == 1) //反审核
                {
                    if (string.IsNullOrEmpty(checker))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据未审核，不能反审核！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    string unauditSql = "UPDATE Gy_SteppedPriceCoefficientBillMain SET " +
                        "HBillStatus = 1, " +
                        "HChecker = '', " +
                        "HCheckDate = NULL " +
                        "WHERE HInterID = " + HInterID;

                    oCN.RunProc(unauditSql, ref DBUtility.ClsPub.sExeReturnInfo);

                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "反审核成功！";
                }

                oCN.Commit();
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "操作失败！" + e.Message;
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 阶梯工价系数 关闭/反关闭功能
        /// <summary>
        /// 关闭/反关闭阶梯工价系数单据
        /// </summary>
        /// <param name="HInterID">单据ID</param>
        /// <param name="IsAudit">关闭(0),反关闭(1)</param>
        /// <param name="user">操作人</param>
        /// <returns></returns>
        [Route("Gy_SteppedPriceCoefficientBill/CloseGy_SteppedPriceCoefficientBill")]
        [HttpGet]
        public object CloseGy_SteppedPriceCoefficientBill(string HInterID, int IsAudit, string user)
        {
            json objJsonResult = new json(); // 使用您现有的json对象
            try
            {
                Console.WriteLine($"=== 关闭/反关闭开始 ===");
                Console.WriteLine($"参数: HInterID={HInterID}, IsAudit={IsAudit}, user={user}");

                // 1. 权限检查
                //string ModRightNameCheck = "Gy_SteppedPriceCoefficientBill_Close";
                //if (!DBUtility.ClsPub.Security_Log(ModRightNameCheck, 1, false, user))
                //{
                //    Console.WriteLine($"权限检查失败: {ModRightNameCheck}");
                //    objJsonResult.code = "0";
                //    objJsonResult.count = 0;
                //    objJsonResult.Message = "无关闭权限！";
                //    objJsonResult.data = null;
                //    return objJsonResult;
                //}

                // 2. 参数验证
                if (string.IsNullOrEmpty(HInterID) || HInterID == "0")
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "单据ID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                // 3. 单据状态检查
                string checkSql = $@"
            SELECT 
                HInterID,
                HBillNo,
                HCloseMan,
                HChecker,
                HCheckDate,
                HDeleteMan,
                HBillStatus,
                HBacker
            FROM Gy_SteppedPriceCoefficientBillMain 
            WHERE HInterID = {HInterID}";

                Console.WriteLine($"查询SQL: {checkSql}");
                ds = oCN.RunProcReturn(checkSql, "CheckBillStatus");

                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "单据不存在！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                DataRow row = ds.Tables[0].Rows[0];
                string billNo = row["HBillNo"]?.ToString() ?? "";
                string closeMan = row["HCloseMan"]?.ToString() ?? "";
                string checker = row["HChecker"]?.ToString() ?? "";
                string deleteMan = row["HDeleteMan"]?.ToString() ?? "";
                string backer = row["HBacker"]?.ToString() ?? "";
                int billStatus = Convert.ToInt32(row["HBillStatus"]);

                Console.WriteLine($"单据状态: BillNo={billNo}, BillStatus={billStatus}, CloseMan={closeMan}, Checker={checker}, DeleteMan={deleteMan}");

                // 4. 业务规则检查
                if (IsAudit == 0)  // 关闭判断
                {
                    // 检查是否已关闭
                    if (billStatus == 3 || !string.IsNullOrEmpty(closeMan))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已关闭！不能再次关闭！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查是否已审核通过（状态2）
                    if (billStatus != 2)
                    {
                        string statusMsg = GetStatusMessage(billStatus);
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = $"单据当前状态为{statusMsg}，只有已审核通过的单据才能关闭！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查是否已作废
                    if (billStatus == 4 || !string.IsNullOrEmpty(deleteMan))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已作废！不能关闭！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查是否已退回
                    if (billStatus == 5 || !string.IsNullOrEmpty(backer))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已审核退回！不能关闭！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                if (IsAudit == 1) // 反关闭判断
                {
                    // 检查是否已关闭
                    if (billStatus != 3 || string.IsNullOrEmpty(closeMan))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据未关闭！不需要反关闭！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                // 5. 开始事务处理
                oCN.BeginTran();

                try
                {
                    if (IsAudit == 0)  // 关闭操作
                    {
                        // 关闭前控制 - 调用存储过程进行业务验证
                        string beforeCloseSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_BeforeCloseCtrl {HInterID}, '{billNo}', '{user}'";
                        Console.WriteLine($"关闭前控制SQL: {beforeCloseSql}");

                        DataSet beforeDs = oCN.RunProcReturn(beforeCloseSql, "BeforeCloseCtrl");

                        if (beforeDs == null || beforeDs.Tables.Count == 0 || beforeDs.Tables[0].Rows.Count == 0)
                        {
                            throw new Exception("关闭前判断失败，请与网络管理人员联系");
                        }

                        if (beforeDs.Tables[0].Rows[0]["HBack"]?.ToString() != "0")
                        {
                            string errorMsg = beforeDs.Tables[0].Rows[0]["HRemark"]?.ToString() ?? "关闭前验证失败";
                            throw new Exception(errorMsg);
                        }

                        // 执行关闭
                        string closeSql = $@"
                    UPDATE Gy_SteppedPriceCoefficientBillMain 
                    SET 
                        HCloseMan = '{user.Replace("'", "''")}',
                        HCloseDate = GETDATE(),
                        HBillStatus = 3  -- 已关闭状态
                    WHERE HInterID = {HInterID}";

                        Console.WriteLine($"关闭SQL: {closeSql}");
                        oCN.RunProc(closeSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        // 关闭后控制
                        string afterCloseSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_AfterCloseCtrl {HInterID}, '{billNo}', '{user}'";
                        oCN.RunProc(afterCloseSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        oCN.Commit();

                        Console.WriteLine("关闭成功完成");

                        objJsonResult.code = "1";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "关闭成功！";
                        objJsonResult.data = new { HInterID = HInterID, HBillNo = billNo };
                        return objJsonResult;
                    }

                    if (IsAudit == 1) // 反关闭操作
                    {
                        // 反关闭前控制
                        string beforeUnCloseSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_BeforeUnCloseCtrl {HInterID}, '{billNo}', '{user}'";
                        Console.WriteLine($"反关闭前控制SQL: {beforeUnCloseSql}");

                        DataSet beforeUnCloseDs = oCN.RunProcReturn(beforeUnCloseSql, "BeforeUnCloseCtrl");

                        if (beforeUnCloseDs == null || beforeUnCloseDs.Tables.Count == 0 || beforeUnCloseDs.Tables[0].Rows.Count == 0)
                        {
                            throw new Exception("反关闭前判断失败，请与网络管理人员联系");
                        }

                        if (beforeUnCloseDs.Tables[0].Rows[0]["HBack"]?.ToString() != "0")
                        {
                            string errorMsg = beforeUnCloseDs.Tables[0].Rows[0]["HRemark"]?.ToString() ?? "反关闭前验证失败";
                            throw new Exception(errorMsg);
                        }

                        // 执行反关闭 - 返回审核通过状态(2)
                        string unCloseSql = $@"
                    UPDATE Gy_SteppedPriceCoefficientBillMain 
                    SET 
                        HCloseMan = '',
                        HCloseDate = NULL,
                        HBillStatus = 2  -- 返回审核通过状态
                    WHERE HInterID = {HInterID}";

                        Console.WriteLine($"反关闭SQL: {unCloseSql}");
                        oCN.RunProc(unCloseSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        // 反关闭后控制
                        string afterUnCloseSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_AfterUnCloseCtrl {HInterID}, '{billNo}', '{user}'";
                        oCN.RunProc(afterUnCloseSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        oCN.Commit();

                        Console.WriteLine("反关闭成功完成");

                        objJsonResult.code = "1";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "反关闭成功！";
                        objJsonResult.data = new { HInterID = HInterID, HBillNo = billNo };
                        return objJsonResult;
                    }

                    // 参数错误
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无效的操作类型！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                catch (Exception ex)
                {
                    oCN.RollBack();
                    Console.WriteLine($"操作失败: {ex.Message}");
                    throw new Exception("操作失败：" + ex.Message);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine($"执行失败: {e.Message}");
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "执行失败！" + e.Message;
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 阶梯工价系数 作废/反作废
        /// <summary>
        /// 作废/反作废阶梯工价系数单据
        /// </summary>
        /// <param name="HInterID">单据ID</param>
        /// <param name="IsAudit">作废(0),反作废(1)</param>
        /// <param name="CurUserName">操作人</param>
        /// <returns></returns>
        [Route("Gy_SteppedPriceCoefficientBill/DropGy_SteppedPriceCoefficientBill")]
        [HttpGet]
        public object DropGy_SteppedPriceCoefficientBill(int HInterID, int IsAudit, string CurUserName)
        {
            json objJsonResult = new json(); // 使用您现有的json对象
            try
            {
                Console.WriteLine($"=== 作废/反作废开始 ===");
                Console.WriteLine($"参数: HInterID={HInterID}, IsAudit={IsAudit}, CurUserName={CurUserName}");

                // 1. 权限检查
                string ModRightNameCheck = "Gy_SteppedPriceCoefficientBill_Delete";
                //if (!DBUtility.ClsPub.Security_Log(ModRightNameCheck, 1, false, CurUserName))
                //{
                //    Console.WriteLine($"权限检查失败: {ModRightNameCheck}");
                //    objJsonResult.code = "0";
                //    objJsonResult.count = 0;
                //    objJsonResult.Message = "作废失败！无权限！";
                //    objJsonResult.data = null;
                //    return objJsonResult;
                //}

                // 2. 参数验证
                if (HInterID <= 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "单据ID无效！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                // 3. 获取单据信息
                string checkSql = $@"
            SELECT 
                HInterID,
                HBillNo,
                HChecker,
                HCheckDate,
                HCloseMan,
                HDeleteMan,
                HBillStatus,
                HBacker,
                HBackDate
            FROM Gy_SteppedPriceCoefficientBillMain 
            WHERE HInterID = {HInterID}";

                Console.WriteLine($"查询SQL: {checkSql}");
                ds = oCN.RunProcReturn(checkSql, "GetBillInfo");

                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "单据不存在！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                DataRow row = ds.Tables[0].Rows[0];
                string billNo = row["HBillNo"]?.ToString() ?? "";
                string checker = row["HChecker"]?.ToString() ?? "";
                string closeMan = row["HCloseMan"]?.ToString() ?? "";
                string deleteMan = row["HDeleteMan"]?.ToString() ?? "";
                string backer = row["HBacker"]?.ToString() ?? "";
                int billStatus = Convert.ToInt32(row["HBillStatus"]);

                Console.WriteLine($"单据状态: BillNo={billNo}, BillStatus={billStatus}, Checker={checker}, CloseMan={closeMan}, DeleteMan={deleteMan}, Backer={backer}");

                // 4. 业务规则检查
                if (IsAudit == 0)  // 作废判断
                {
                    // 检查是否已作废
                    if (billStatus == 4 || !string.IsNullOrEmpty(deleteMan))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已作废！不能再次作废！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查是否已审核通过（状态2）
                    if (billStatus == 2)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已审核通过！请先反审核后再作废！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查是否已关闭（状态3）
                    if (billStatus == 3)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据已关闭！请先反关闭后再作废！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查特殊状态（不允许作废的状态）
                    int[] notAllowedStatus = { 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17 };
                    if (notAllowedStatus.Contains(billStatus))
                    {
                        string statusMsg = GetStatusMessage(billStatus);
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = $"单据当前状态为{statusMsg}，不允许作废！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                if (IsAudit == 1) // 反作废判断
                {
                    // 检查是否已作废
                    if (billStatus != 4 || string.IsNullOrEmpty(deleteMan))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "单据未作废！不需要反作废！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    // 检查作废后是否已被其他操作影响
                    object checkDateObj = row["HCheckDate"];
                    if (!string.IsNullOrEmpty(checker) && checkDateObj != DBNull.Value && checkDateObj != null)
                    {
                        DateTime checkDate = Convert.ToDateTime(checkDateObj);
                        object deleteDateObj = row["HDeleteDate"];
                        if (deleteDateObj != DBNull.Value && deleteDateObj != null)
                        {
                            DateTime deleteDate = Convert.ToDateTime(deleteDateObj);
                            if (checkDate > deleteDate)
                            {
                                objJsonResult.code = "0";
                                objJsonResult.count = 0;
                                objJsonResult.Message = "单据作废后有审核记录，不能反作废！";
                                objJsonResult.data = null;
                                return objJsonResult;
                            }
                        }
                    }
                }

                // 5. 开始事务处理
                oCN.BeginTran();

                try
                {
                    if (IsAudit == 0) // 作废操作
                    {
                        // 作废前控制
                        string beforeDropSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_BeforeDropCtrl {HInterID}, '{billNo}', '{CurUserName}'";
                        Console.WriteLine($"作废前控制SQL: {beforeDropSql}");

                        DataSet beforeDropDs = oCN.RunProcReturn(beforeDropSql, "BeforeDropCtrl");

                        if (beforeDropDs == null || beforeDropDs.Tables.Count == 0 || beforeDropDs.Tables[0].Rows.Count == 0)
                        {
                            throw new Exception("作废前判断失败，请与网络管理人员联系");
                        }

                        if (beforeDropDs.Tables[0].Rows[0]["HBack"]?.ToString() != "0")
                        {
                            string errorMsg = beforeDropDs.Tables[0].Rows[0]["HRemark"]?.ToString() ?? "作废前验证失败";
                            throw new Exception(errorMsg);
                        }

                        // 获取当前状态，用于记录日志
                        string originalStatus = GetStatusMessage(billStatus);

                        // 执行作废
                        string dropSql = $@"
                    UPDATE Gy_SteppedPriceCoefficientBillMain 
                    SET 
                        HDeleteMan = '{CurUserName.Replace("'", "''")}',
                        HDeleteDate = GETDATE(),
                        HBillStatus = 4  -- 已作废状态
                    WHERE HInterID = {HInterID}";

                        Console.WriteLine($"作废SQL: {dropSql}");
                        oCN.RunProc(dropSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        // 记录作废日志
                        string logSql = $@"
                    INSERT INTO Gy_SteppedPriceCoefficientBillLog (
                        HInterID, HBillNo, HOperation, HOperator,
                        HOperationDate, HOriginalStatus, HNewStatus,
                        HRemark
                    ) VALUES (
                        {HInterID}, '{billNo}', '作废', '{CurUserName.Replace("'", "''")}',
                        GETDATE(), '{originalStatus}', '已作废',
                        '单据从{originalStatus}状态作废'
                    )";
                        oCN.RunProc(logSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        // 作废后控制
                        string afterDropSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_AfterDropCtrl {HInterID}, '{billNo}', '{CurUserName}'";
                        oCN.RunProc(afterDropSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        oCN.Commit();

                        Console.WriteLine("作废成功完成");

                        objJsonResult.code = "1";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "作废成功！";
                        objJsonResult.data = new { HInterID = HInterID, HBillNo = billNo };
                        return objJsonResult;
                    }

                    if (IsAudit == 1) // 反作废操作
                    {
                        // 反作废前控制
                        string beforeUnDropSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_BeforeUnDropCtrl {HInterID}, '{billNo}', '{CurUserName}'";
                        Console.WriteLine($"反作废前控制SQL: {beforeUnDropSql}");

                        DataSet beforeUnDropDs = oCN.RunProcReturn(beforeUnDropSql, "BeforeUnDropCtrl");

                        if (beforeUnDropDs == null || beforeUnDropDs.Tables.Count == 0 || beforeUnDropDs.Tables[0].Rows.Count == 0)
                        {
                            throw new Exception("反作废前判断失败，请与网络管理人员联系");
                        }

                        if (beforeUnDropDs.Tables[0].Rows[0]["HBack"]?.ToString() != "0")
                        {
                            string errorMsg = beforeUnDropDs.Tables[0].Rows[0]["HRemark"]?.ToString() ?? "反作废前验证失败";
                            throw new Exception(errorMsg);
                        }

                        // 获取原状态（从日志中获取或根据情况判断）
                        int originalStatus = 1; // 默认返回未审核状态

                        // 尝试从日志中获取作废前的状态
                        string getOriginalStatusSql = $@"
                    SELECT TOP 1 HOriginalStatus 
                    FROM Gy_SteppedPriceCoefficientBillLog 
                    WHERE HInterID = {HInterID} AND HOperation = '作废' 
                    ORDER BY HOperationDate DESC";

                        DataSet statusDs = oCN.RunProcReturn(getOriginalStatusSql, "GetOriginalStatus");
                        if (statusDs != null && statusDs.Tables[0].Rows.Count > 0)
                        {
                            string originalStatusStr = statusDs.Tables[0].Rows[0]["HOriginalStatus"]?.ToString() ?? "";
                            originalStatus = GetStatusFromMessage(originalStatusStr);
                        }

                        // 执行反作废
                        string unDropSql = $@"
                    UPDATE Gy_SteppedPriceCoefficientBillMain 
                    SET 
                        HDeleteMan = '',
                        HDeleteDate = NULL,
                        HBillStatus = {originalStatus}  -- 返回作废前的状态
                    WHERE HInterID = {HInterID}";

                        Console.WriteLine($"反作废SQL: {unDropSql}");
                        oCN.RunProc(unDropSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        // 记录反作废日志
                        string newStatusMsg = GetStatusMessage(originalStatus);
                        string logSql = $@"
                    INSERT INTO Gy_SteppedPriceCoefficientBillLog (
                        HInterID, HBillNo, HOperation, HOperator,
                        HOperationDate, HOriginalStatus, HNewStatus,
                        HRemark
                    ) VALUES (
                        {HInterID}, '{billNo}', '反作废', '{CurUserName.Replace("'", "''")}',
                        GETDATE(), '已作废', '{newStatusMsg}',
                        '单据反作废，恢复为{newStatusMsg}状态'
                    )";
                        oCN.RunProc(logSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        // 反作废后控制
                        string afterUnDropSql = $"exec h_p_Gy_SteppedPriceCoefficientBill_AfterUnDropCtrl {HInterID}, '{billNo}', '{CurUserName}'";
                        oCN.RunProc(afterUnDropSql, ref DBUtility.ClsPub.sExeReturnInfo);

                        oCN.Commit();

                        Console.WriteLine("反作废成功完成");

                        objJsonResult.code = "1";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "反作废成功！";
                        objJsonResult.data = new { HInterID = HInterID, HBillNo = billNo };
                        return objJsonResult;
                    }

                    // 参数错误
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无效的操作类型！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                catch (Exception ex)
                {
                    oCN.RollBack();
                    Console.WriteLine($"操作失败: {ex.Message}");
                    throw new Exception("操作失败：" + ex.Message);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine($"执行失败: {e.Message}");
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "执行失败！" + e.Message;
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 辅助方法 - 状态转换（放在类级别）
        /// <summary>
        /// 获取状态对应的文字描述
        /// </summary>
        private string GetStatusMessage(int status)
        {
            switch (status)
            {
                case 1: return "未审";
                case 2: return "审核通过";
                case 3: return "关闭";
                case 4: return "作废";
                case 5: return "审核退回";
                case 6: return "审核中";
                case 7: return "已阅";
                case 8: return "已回复";
                case 9: return "结案";
                case 10: return "验证";
                case 11: return "下达";
                case 12: return "开工";
                case 13: return "申请审批";
                case 15: return "申请检验";
                case 16: return "判定合格";
                case 17: return "判定不合格";
                default: return "未知状态";
            }
        }

        /// <summary>
        /// 根据状态描述获取状态值
        /// </summary>
        private int GetStatusFromMessage(string statusMessage)
        {
            switch (statusMessage)
            {
                case "未审": return 1;
                case "审核通过": return 2;
                case "关闭": return 3;
                case "作废": return 4;
                case "审核退回": return 5;
                case "审核中": return 6;
                case "已阅": return 7;
                case "已回复": return 8;
                case "结案": return 9;
                case "验证": return 10;
                case "下达": return 11;
                case "开工": return 12;
                case "申请审批": return 13;
                case "申请检验": return 15;
                case "判定合格": return 16;
                case "判定不合格": return 17;
                default: return 1; // 默认返回未审
            }
        }

        /// <summary>
        /// 检查是否可以作废
        /// </summary>
        private bool CanDropBill(int billStatus)
        {
            // 允许作废的状态
            int[] allowedStatus = { 1, 5, 6, 7, 8 }; // 未审、审核退回、审核中、已阅、已回复
            return allowedStatus.Contains(billStatus);
        }
        #endregion

        #region 数据模型类
        public class Gy_SteppedPriceCoefficient
        {
            //主表字段
            public long HInterID { get; set; }
            public int HYear { get; set; }
            public int HPeriod { get; set; }
            public string HBillType { get; set; }
            public string HBillSubType { get; set; }
            public DateTime HDate { get; set; }
            public string HBillNo { get; set; }
            public int HBillStatus { get; set; }
            public string HRemark { get; set; }
            public int HProcID { get; set; }
            public int HEmpID { get; set; }
            public int HDeptID { get; set; }
            public int HStockOrgID { get; set; }

            //子表集合
            public List<Gy_SteppedPriceCoefficientSub> SubItems { get; set; }
        }

        public class Gy_SteppedPriceCoefficientSub
        {
            public long HEntryID { get; set; }
            public int HSeq { get; set; }
            public int HMinQty { get; set; }
            public int HMaxQty { get; set; }
            public decimal HPriceCoefficient { get; set; }
            public decimal HMaxPrice { get; set; }
            public string HRemark { get; set; }
        }
        #endregion
    }
}